package com.gjm.tool.utils;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.alibaba.fastjson.JSON;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.commons.lang3.StringUtils;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

public class FileTestEqWithMysql {

    public static void main(String[] args) throws Exception {
        /**
         * 比对四个字段是否一致
         *  1. 读取我侧改数脚本-四个分别读
         *  2. 读取主数据数据,以businessCode key,以行数据为value,获取map
         *  3. 四个改数脚本,分别便利,获取两个值,businessCode 和  set的值,然后根据businessCode为key 从2的map中获取主数据的值,然后与刚刚获取set的值做对比,讲不等的值提取出来
         */
        List<List<Object>> mysql = mysql();
        HashMap<String, List<List<Object>>> mysqlMap = new HashMap<>(); //数据库certNo
        for (List<Object> row : mysql) {
            String businessCode = String.valueOf(row.get(0));
            if (StringUtils.isNotBlank(businessCode)){
                List<List<Object>> list = mysqlMap.get(businessCode);
                if (!mysqlMap.containsKey(businessCode)) {
                    list = new ArrayList<>();
                }
                list.add(row);
                mysqlMap.put(businessCode, list);
            }
        }

        //1. 统一社会信用编码
        Map<String, String> certNoMap = sqlReadOneWithCertNo();

        for (Map.Entry<String, String> entry : certNoMap.entrySet()) {
            String sqlBusinessCode = entry.getKey();
            //获取生产环境的值
            List<List<Object>> mysqlList = mysqlMap.get(sqlBusinessCode);
            if (CollectionUtil.isEmpty(mysqlList)){
                System.out.println("统一社会信用编码问题一:" + sqlBusinessCode + "不存在");
            }else {
                Set<String> set = new HashSet<>();
                List<String> list = new ArrayList<>();
                for (List<Object> line : mysqlList) {
                    set.add(String.valueOf(line.get(1)));
                    list.add(String.valueOf(line.get(1)));
                }
                if (set.size() > 1 || set.size() == 0){
                    System.out.println("统一社会信用编码问题二:" + sqlBusinessCode + "未刷数成功1");
                }else {

                    if (!list.get(0).equals(entry.getValue())){
                        System.out.println("统一社会信用编码问题三:" + sqlBusinessCode + "未刷数成功2");
                    }
                }
            }
        }

        //2. 成立日期
        Map<String, String> buildDateMap = sqlReadOneWithBuildDate();

        for (Map.Entry<String, String> entry : buildDateMap.entrySet()) {
            String sqlBusinessCode = entry.getKey();
            //获取生产环境的值
            List<List<Object>> mysqlList = mysqlMap.get(sqlBusinessCode);
            if (CollectionUtil.isEmpty(mysqlList)){
                System.out.println("成立日期问题一:" + sqlBusinessCode + "不存在");
            }else {
                Set<String> set = new HashSet<>();
                List<String> list = new ArrayList<>();
                for (List<Object> line : mysqlList) {
                    set.add(String.valueOf(line.get(2)));
                    list.add(String.valueOf(line.get(2)));
                }
                if (set.size() > 1 || set.size() == 0){
                    System.out.println("成立日期问题二:" + sqlBusinessCode + "未刷数成功1");
                }else {

                    if (!list.get(0).equals(entry.getValue())){
                        System.out.println("成立日期问题三:" + sqlBusinessCode + "未刷数成功2");
                    }
                }
            }
        }

        //3. 注册资本
        Map<String, String> registerCostMap = sqlReadOneWithRegisterCost();

        for (Map.Entry<String, String> entry : registerCostMap.entrySet()) {
            String sqlBusinessCode = entry.getKey();
            //获取生产环境的值
            List<List<Object>> mysqlList = mysqlMap.get(sqlBusinessCode);
            if (CollectionUtil.isEmpty(mysqlList)){
                System.out.println("注册资本问题一:" + sqlBusinessCode + "不存在");
            }else {
                Set<String> set = new HashSet<>();
                List<String> list = new ArrayList<>();
                for (List<Object> line : mysqlList) {
                    set.add(String.valueOf(line.get(3)));
                    list.add(String.valueOf(line.get(3)));
                }
                if (set.size() > 1 || set.size() == 0){
                    System.out.println("注册资本问题二:" + sqlBusinessCode + "未刷数成功1");
                }else {

                    if (!list.get(0).equals(entry.getValue())){
                        System.out.println("注册资本问题三:" + sqlBusinessCode + "未刷数成功2");
                    }
                }
            }
        }
        //4. 法人代表
        Map<String, String> legalPersonMap = sqlReadOneWithLegalPerson();

        for (Map.Entry<String, String> entry : legalPersonMap.entrySet()) {
            String sqlBusinessCode = entry.getKey();
            //获取生产环境的值
            List<List<Object>> mysqlList = mysqlMap.get(sqlBusinessCode);
            if (CollectionUtil.isEmpty(mysqlList)){
                System.out.println("法人代表问题一:" + sqlBusinessCode + "不存在");
            }else {
                Set<String> set = new HashSet<>();
                List<String> list = new ArrayList<>();
                for (List<Object> line : mysqlList) {
                    set.add(String.valueOf(line.get(4)));
                    list.add(String.valueOf(line.get(4)));
                }
                if (set.size() > 1 || set.size() == 0){
                    System.out.println("法人代表问题二:" + sqlBusinessCode + "未刷数成功1");
                }else {

                    if (!list.get(0).equals(entry.getValue())){
                        System.out.println("法人代表问题三:" + sqlBusinessCode + "未刷数成功2");
                    }
                }
            }
        }

        System.out.println("程序结束");

    }


    /**
     *
     * 读取改数脚本--统一社会行用编码
     * update  ctc_cc_cust_info_t set cert_no= '91520502MA6DJ08T1L' where  business_code='020100414747';
     * (?<=cert_no= ')([^']+)
     * (?<=business_code=')(.*?)(?=')
     */
    public static Map<String, String> sqlReadOneWithCertNo() throws Exception {
        String filePath = "C:\\Users\\50300\\OneDrive\\桌面\\司库刷数\\客商和客户可以刷数的脚本\\客商表脚本V1.1\\统一社会信用代码可以刷数的数据.txt";
        Map map = new HashMap();
        List<String> list = FileUtil.readInfoFromFile(filePath);
        for (String string : list) {
            List<String> certNoList = find("(?<=cert_no= ')([^']+)", string);
            List<String> businessCodeList = find("(?<=business_code=')(.*?)(?=')", string);
            if (CollectionUtil.isEmpty(certNoList) || CollectionUtil.isEmpty(businessCodeList)) {
                continue;
            }
            String certNo = certNoList.get(0);
            String businessCode = businessCodeList.get(0);
            map.put(businessCode, certNo);
        }
        return map;
    }
    public static Map<String, String> sqlReadOneWithBuildDate() throws Exception {
        String filePath = "C:\\Users\\50300\\OneDrive\\桌面\\司库刷数\\客商和客户可以刷数的脚本\\客商表脚本V1.1\\成立日期可以刷数的数据.txt";
        Map map = new HashMap();
        List<String> list = FileUtil.readInfoFromFile(filePath);
        for (String string : list) {
            List<String> certNoList = find("(?<=build_data= ')([^']+)", string);
            List<String> businessCodeList = find("(?<=business_code=')(.*?)(?=')", string);
            if (CollectionUtil.isEmpty(certNoList) || CollectionUtil.isEmpty(businessCodeList)) {
                continue;
            }
            String certNo = certNoList.get(0);
            String businessCode = businessCodeList.get(0);
            map.put(businessCode, certNo);
        }
        return map;
    }
    public static Map<String, String> sqlReadOneWithRegisterCost() throws Exception {
        String filePath = "C:\\Users\\50300\\OneDrive\\桌面\\司库刷数\\客商和客户可以刷数的脚本\\客商表脚本V1.1\\注册资本可以刷数的数据.txt";
        Map map = new HashMap();
        List<String> list = FileUtil.readInfoFromFile(filePath);
        for (String string : list) {
            List<String> certNoList = find("(?<=register_cost= ')([^']+)", string);
            List<String> businessCodeList = find("(?<=business_code=')(.*?)(?=')", string);
            if (CollectionUtil.isEmpty(certNoList) || CollectionUtil.isEmpty(businessCodeList)) {
                continue;
            }
            String certNo = certNoList.get(0);
            String businessCode = businessCodeList.get(0);
            map.put(businessCode, certNo);
        }
        return map;
    }
    public static Map<String, String> sqlReadOneWithLegalPerson() throws Exception {
        String filePath = "C:\\Users\\50300\\OneDrive\\桌面\\司库刷数\\客商和客户可以刷数的脚本\\客商表脚本V1.1\\法定代表人可以刷数的数据.txt";
        Map map = new HashMap();
        List<String> list = FileUtil.readInfoFromFile(filePath);
        for (String string : list) {
            List<String> certNoList = find("(?<=legal_person= ')([^']+)", string);
            List<String> businessCodeList = find("(?<=business_code=')(.*?)(?=')", string);
            if (CollectionUtil.isEmpty(certNoList) || CollectionUtil.isEmpty(businessCodeList)) {
                continue;
            }
            String certNo = certNoList.get(0);
            String businessCode = businessCodeList.get(0);
            map.put(businessCode, certNo);
        }
        return map;
    }
    /**
     * @param regex 正则表达式字符串
     * @param str   要匹配的字符串
     * @return;
     */
    public static List<String> find(String regex, String str) {
        List<String> list = new ArrayList<>();
        if (StrUtil.isBlank(regex) || StrUtil.isBlank(str)) {
            return list;
        }
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(str);
        while (matcher.find()) {
            list.add(matcher.group());
        }
        return list;
    }


    public static List<List<Object>> mysql() {
        String filePath = "C:\\Users\\50300\\OneDrive\\桌面\\司库刷数\\客商925司库刷数后数据.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        return sheetList.subList(1, sheetList.size());
    }

    public static List<List<Object>> mainDataWithSheet1() {
        String filePath = "C:\\Users\\50300\\OneDrive\\桌面\\司库刷数\\客户与客商信息不一致数据清单_20240922.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath, 1);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        return sheetList.subList(2, sheetList.size());
    }

    public static List<List<Object>> mainDataWithSheet2() {
        String filePath = "C:\\Users\\50300\\OneDrive\\桌面\\司库刷数\\客户与客商信息不一致数据清单_20240922.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath, 2);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        return sheetList.subList(2, sheetList.size());
    }

    public static List<List<Object>> mainDataWithSheet3() {
        String filePath = "C:\\Users\\50300\\OneDrive\\桌面\\司库刷数\\客户与客商信息不一致数据清单_20240922.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath, 3);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        return sheetList.subList(2, sheetList.size());
    }

    public static List<List<Object>> mainDataWithSheet4() {
        String filePath = "C:\\Users\\50300\\OneDrive\\桌面\\司库刷数\\客户与客商信息不一致数据清单_20240922.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath, 4);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        return sheetList.subList(2, sheetList.size());
    }

    public static Set<String> mainData( ) {
        HashSet<String>  set= new HashSet<>();
        String filePath = "C:\\Users\\50300\\OneDrive\\桌面\\司库刷数\\客户与客商信息不一致数据清单_20240922.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath, 1);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        List<List<Object>> list = sheetList.subList(2, sheetList.size());
        List<MainVO> mainList = new ArrayList<>();
        for (List<Object> objects : list) {
            mainList.add(new MainVO(String.valueOf(objects.get(1)), String.valueOf(objects.get(2)), String.valueOf(objects.get(3))));
        }
        Map<String, List<MainVO>> map = mainList.stream().collect(Collectors.groupingBy(MainVO::getCustCode));
        for (Map.Entry<String, List<MainVO>> entry : map.entrySet()) {
            if (CollectionUtil.isEmpty(entry.getValue()) || entry.getValue().size() <= 1) {
                continue;
            }
            Set<String> nameSet = entry.getValue().stream().map(i -> i.getCustName()).collect(Collectors.toSet());
            Set<String> certNoSet = entry.getValue().stream().map(i -> i.getCertNo()).collect(Collectors.toSet());

            if (nameSet.size() > 1 || certNoSet.size() > 1) {
                for (MainVO mainVO : entry.getValue()) {
                    set.add(mainVO.getCustCode());
                    System.out.println(JSON.toJSONString(mainVO));
                }

            }
        }
        return set;
    }

    /**
     *
     * @param problemSet 是businessCode
     * @param row
     */
    public static void fileExportWithSqlShell(Set<String> problemSet, List<Object> row, HashMap<String, List<String>> map) {
        if (problemSet.contains(String.valueOf(row.get(1)))) {
            System.out.println("fileExportWithSqlShell:" + row.get(1));
            return;
        }
        List<String> strings = map.get(String.valueOf(row.get(3)));
        if (CollectionUtil.isEmpty(strings)) {
            strings = new ArrayList<>();
        }
        strings.add(String.valueOf(row.get(1)));
        map.put(String.valueOf(row.get(3)), strings);
    }

    @AllArgsConstructor
    @NoArgsConstructor
    @Data
    static class MainVO {
        private String custCode;
        private String custName;
        private String certNo;
    }


}



